﻿using Microsoft.Extensions.DependencyInjection;
using Modules.System.User;
using PetaPoco;
using PmSoft;
using PmSoft.ApplicationContext;
using PmSoft.Caching;
using PmSoft.DBContext;
using PmSoft.Entity;
using PmSoft.Repositories;
using PmSoft.Utilities;

namespace Modules.Person.Repositories;

[UseDI(ServiceLifetime.Scoped)]
public class PersonBankRepository : Repository<PersonBank, DefaultDbContext>
{
    private readonly IDatabase dbContext;
    private readonly IApplicationContext applicationContext;

    public PersonBankRepository(DefaultDbContext dbContext, IApplicationContext applicationContext) : base(dbContext, applicationContext)
    {
        this.dbContext = dbContext;
        this.applicationContext = applicationContext;
    }

    /// <summary>
    /// 创建或更新
    /// </summary>
    /// <param name="bank"></param>
    /// <exception cref="ArgumentNullException"></exception>
    public void CreateOrUpdate(PersonBank bank)
    {
        if (string.IsNullOrEmpty(bank.IdCard))
            throw new ArgumentNullException(nameof(bank.IdCard));

        Sql sql = Sql.Builder;
        sql.Select("id")
            .From("person_bank")
            .Where("idcard=@0", bank.IdCard);

        int id = dbContext.FirstOrDefault<int>(sql);
        if (id > 0)
        {
            bank.Id = id;
            base.Update(bank);
        }
        else
            base.Insert(bank);
    }

    public IPagedList<PersonBank> GetPersonBanks(PersonBankQuery query, int pageIndex, int pageSize)
    {
        Sql sql = Sql.Builder;
        sql.Select(" distinct person_bank.id")
            .From("person_bank");
        sql.InnerJoin("person as b").On("person_bank.idcard=b.card_number");
        sql.InnerJoin("project_group_member as c").On("b.person_id=c.person_id");
        sql.InnerJoin("project_group as d").On("c.group_id=d.group_id");

        #region 主管单位数据权限

        if (applicationContext.CurrentUser is IDeptUser deptUser && !deptUser.IsSuperAdministrator())
        {
            sql.InnerJoin("project as prj").On($"c.project_id=prj.project_id and prj.is_deleted=0 and prj.industry_type=@0 ", deptUser.IndustryType);
            if (deptUser.Level == AdministrativeLevel.City)
                sql.Append($" and prj.city_code={deptUser.CityCode}");
            if (deptUser.Level == AdministrativeLevel.Area)
                sql.Append($" and prj.area_code={deptUser.AreaCode}");
        }

        #endregion 主管单位数据权限

        if (!string.IsNullOrEmpty(query.Keywords))
            sql.Where("(b.name Like @0 or person_bank.idcard like @0 or person_bank.card_no like @0 or d.name like @0)", "%" + StringUtility.StripSQLInjection(query.Keywords) + "%");
        if (query.CorpId.HasValue)
            sql.Where("c.corp_id=@0", query.CorpId);
        if (query.ProjectId.HasValue)
            sql.Where("c.project_id=@0", query.ProjectId);
        if (query.CardType.HasValue)
            sql.Where("person_bank.type=@0", query.CardType.Value);
        if (query.Status.HasValue)
            sql.Where("c.status=@0", query.Status);
        sql.GroupBy("card_no");
        sql.OrderBy("person_bank.create_time DESC");
        return GetPagingEntities(pageSize, pageIndex, sql);
    }

    public PersonBank? GetPersonBankByCardNo(string cardNo)
    {
        Sql sql = Sql.Builder.Select("id").From("person_bank").Where("card_no=@0", cardNo);
        var id = DbContext.FirstOrDefault<int>(sql);
        if (id == 0)
            return null;
        return Get(id);
    }

    /// <summary>
    /// 获取身份证下所有银行卡信息
    /// </summary>
    /// <param name="idCard"></param>
    /// <returns></returns>
    public IEnumerable<PersonBank> GetPersonBanks(string idCard)
    {
        int areaVersion = EntityCacheService.GetAreaVersion("IdCard", idCard);
        return GetTopEntities(1000, CachingExpirationType.ObjectCollection, () =>
        {
            return $"PersonBanks:{idCard}:{areaVersion}";
        }, () =>
        {
            Sql sql = Sql.Builder.Select("id").From("person_bank").Where("idcard=@0", idCard);
            return sql;
        });
    }

    /// <summary>
    /// 获取当前项目下对应人员的数据
    /// </summary>
    /// <param name="ids"></param>
    /// <param name="projectId"></param>
    /// <returns></returns>
    public IEnumerable<PersonBank> GetPersonBanksByIdsProjectId(IEnumerable<int> ids, int projectId)
    {
        if (ids == null || ids.Count() == 0)
            return Enumerable.Empty<PersonBank>();
        Sql sql = Sql.Builder;
        sql.Select("person_bank.id")
            .From("person_bank");
        sql.InnerJoin("person as b").On("person_bank.idcard=b.card_number");
        sql.InnerJoin("project_group_member as c").On("b.person_id=c.person_id");
        sql.Where("c.project_id=@0 and person_bank.id in (@1)", projectId, ids);
        sql.OrderBy("person_bank.create_time DESC");

        return PopulateEntitiesByEntityIds(DbContext.Fetch<int>(sql));
    }

    public bool Exists(PersonBank bank)
    {
        if (string.IsNullOrEmpty(bank.IdCard))
            return false;

        if (bank.Id > 0)
            return DbContext.Exists<PersonBank>("idcard=@0 AND card_no=@1 AND id<>@2 ", bank.IdCard, bank.CardNo, bank.Id);
        else
            return DbContext.Exists<PersonBank>("idcard=@0 AND card_no=@1", bank.IdCard, bank.CardNo);
    }

    /// <summary>
    /// 通过身份证号集合获取关联银行卡
    /// </summary>
    /// <param name="idCards"></param>
    /// <returns></returns>
    public IEnumerable<PersonBank> GetPersonBanksByIdCards(IEnumerable<string> idCards)
    {
        if (idCards == null || idCards.Count() == 0)
            return Enumerable.Empty<PersonBank>();
        Sql sql = Sql.Builder;
        sql.Select("id")
            .From("person_bank");
        if (idCards.Any())
            sql.Where("idcard in(@0)", idCards);
        var ids = DbContext.FetchFirstColumn<int>(sql);

        return PopulateEntitiesByEntityIds(ids);
    }
}